/*
 * Copyright (c) 2012. The Genome Analysis Centre, Norwich, UK
 * MISO project contacts: Robert Davey, Mario Caccamo @ TGAC
 * *********************************************************************
 *
 * This file is part of MISO.
 *
 * MISO is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * MISO is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with MISO.  If not, see <http://www.gnu.org/licenses/>.
 *
 * *********************************************************************
 */

package uk.ac.bbsrc.tgac.miso.sqlstore;

import com.eaglegenomics.simlims.core.Note;
import net.sf.ehcache.CacheManager;
import net.sf.ehcache.Element;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DuplicateKeyException;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.simple.SimpleJdbcInsert;
import uk.ac.bbsrc.tgac.miso.core.data.AbstractKit;
import uk.ac.bbsrc.tgac.miso.core.data.Kit;
import uk.ac.bbsrc.tgac.miso.core.data.Platform;
import uk.ac.bbsrc.tgac.miso.core.data.impl.kit.*;
import uk.ac.bbsrc.tgac.miso.core.data.type.KitType;
import uk.ac.bbsrc.tgac.miso.core.data.type.PlatformType;
import uk.ac.bbsrc.tgac.miso.core.factory.DataObjectFactory;
import uk.ac.bbsrc.tgac.miso.core.store.ExperimentStore;
import uk.ac.bbsrc.tgac.miso.core.store.KitStore;
import uk.ac.bbsrc.tgac.miso.core.store.NoteStore;
import uk.ac.bbsrc.tgac.miso.sqlstore.cache.CacheAwareRowMapper;
import uk.ac.bbsrc.tgac.miso.sqlstore.util.DbUtils;

import javax.persistence.CascadeType;
import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Collection;
import java.util.List;
import java.util.Map;

/**
 * uk.ac.bbsrc.tgac.miso.sqlstore
 * <p/>
 * Info
 *
 * @author Rob Davey
 * @since 0.0.2
 */
public class SQLKitDAO implements KitStore {
  private static final String TABLE_NAME = "Kit";

  public static final String KITS_SELECT =
          "SELECT kitId, identificationBarcode, locationBarcode, lotNumber, kitDate, kitDescriptorId " +
          "FROM "+TABLE_NAME;

  public static final String KIT_SELECT_BY_ID =
          KITS_SELECT + " WHERE kitId = ?";

  public static final String KIT_SELECT_BY_BARCODE =
          KITS_SELECT + " WHERE identificationBarcode = ?";

  public static final String KIT_SELECT_BY_LOT_NUMBER =
          KITS_SELECT + " WHERE lotNumber = ?";  

  public static final String KITS_SELECT_BY_TYPE =
          "SELECT k.kitId, k.identificationBarcode, k.locationBarcode, k.lotNumber, k.kitDate, k.kitDescriptorId, ek.experiments_experimentId " +
          "FROM "+TABLE_NAME+" k, Experiment_Kit ek " +
          "WHERE ek.kits_kitId=k.kitId " +
          "AND ek.experiments_experimentId=?";

  public static final String KITS_SELECT_BY_MANUFACTURER =
          "SELECT k.kitId, k.identificationBarcode, k.locationBarcode, k.lotNumber, k.kitDate, k.kitDescriptorId, ek.experiments_experimentId " +
          "FROM "+TABLE_NAME+" k, Experiment_Kit ek " +
          "WHERE ek.kits_kitId=k.kitId " +
          "AND ek.experiments_experimentId=?";

  public static final String KITS_SELECT_BY_RELATED_EXPERIMENT =
          "SELECT k.kitId, k.identificationBarcode, k.locationBarcode, k.lotNumber, k.kitDate, k.kitDescriptorId, ek.experiments_experimentId " +
          "FROM "+TABLE_NAME+" k, Experiment_Kit ek " +
          "WHERE ek.kits_kitId=k.kitId " +
          "AND ek.experiments_experimentId=?";

  public static final String KITS_SELECT_BY_RELATED_LIBRARY =
          "SELECT k.kitId, k.identificationBarcode, k.locationBarcode, k.lotNumber, k.kitDate, k.kitDescriptorId, ek.experiments_experimentId " +
          "FROM "+TABLE_NAME+" k, Library_Kit lk " +
          "WHERE lk.kits_kitId=k.kitId " +
          "AND lk.libraries_libraryId=?";

  public static final String KIT_UPDATE =
          "UPDATE "+TABLE_NAME+" " +
          "SET identificationBarcode=:identificationBarcode, locationBarcode=:locationBarcode, lotNumber=:lotNumber, kitDate=:kitDate, kitDescriptorId=:kitDescriptorId " +
          "WHERE kitId=:kitId";

  public static final String KIT_DESCRIPTORS_SELECT =
          "SELECT kitDescriptorId, name, version, manufacturer, partNumber, stockLevel, kitType, platformType " +
          "FROM KitDescriptor";

  public static final String KIT_DESCRIPTOR_SELECT_BY_ID =
          KIT_DESCRIPTORS_SELECT + " WHERE kitDescriptorId=?";

  public static final String KIT_DESCRIPTOR_SELECT_BY_PART_NUMBER =
          KIT_DESCRIPTORS_SELECT + " WHERE partNumber=?";  

  public static final String KIT_DESCRIPTORS_SELECT_BY_TYPE =
          KIT_DESCRIPTORS_SELECT + " WHERE kitType = ?";

  public static final String KIT_DESCRIPTORS_SELECT_BY_PLATFORM =
          KIT_DESCRIPTORS_SELECT + " WHERE platformType = ?";

  public static final String KIT_DESCRIPTOR_UPDATE =
          "UPDATE KitDescriptor " +
          "SET name=:name, version=:version, manufacturer=:manufacturer, partNumber=:partNumber, stockLevel=:stockLevel, kitType=:kitType, platformType=:platformType " +
          "WHERE kitDescriptorId=:kitDescriptorId";  

  protected static final Logger log = LoggerFactory.getLogger(SQLKitDAO.class);
  private JdbcTemplate template;
  private NoteStore noteDAO;
  private CascadeType cascadeType;

  @Autowired
  private CacheManager cacheManager;

  public void setCacheManager(CacheManager cacheManager) {
    this.cacheManager = cacheManager;
  }

  @Autowired
  private DataObjectFactory dataObjectFactory;

  public void setDataObjectFactory(DataObjectFactory dataObjectFactory) {
    this.dataObjectFactory = dataObjectFactory;
  }

  public void setNoteDAO(NoteStore noteDAO) {
    this.noteDAO = noteDAO;
  }

  public JdbcTemplate getJdbcTemplate() {
    return template;
  }

  public void setJdbcTemplate(JdbcTemplate template) {
    this.template = template;
  }

  public void setCascadeType(CascadeType cascadeType) {
    this.cascadeType = cascadeType;
  }

  public Kit get(long id) throws IOException {
    List eResults = template.query(KIT_SELECT_BY_ID, new Object[]{id}, new KitMapper());
    return eResults.size() > 0 ? (Kit) eResults.get(0) : null;
  }

  @Override
  public Kit lazyGet(long id) throws IOException {
    return get(id);
  }

  public Kit getKitByIdentificationBarcode(String barcode) throws IOException {
    List eResults = template.query(KIT_SELECT_BY_BARCODE, new Object[]{barcode}, new KitMapper());
    return eResults.size() > 0 ? (Kit) eResults.get(0) : null;
  }

  public Kit getKitByLotNumber(String lotNumber) throws IOException {
    List eResults = template.query(KIT_SELECT_BY_LOT_NUMBER, new Object[]{lotNumber}, new KitMapper());
    return eResults.size() > 0 ? (Kit) eResults.get(0) : null;
  }

  public Collection<Kit> listAll() throws IOException {
    return template.query(KITS_SELECT, new KitMapper());
  }

  @Override
  public int count() throws IOException {
    return template.queryForInt("SELECT count(*) FROM "+TABLE_NAME);
  }

  public List<Kit> listByExperiment(long experimentId) throws IOException {
    return template.query(KITS_SELECT_BY_RELATED_EXPERIMENT, new Object[]{experimentId}, new KitMapper());
  }

  public List<Kit> listByLibrary(long libraryId) throws IOException {
    return template.query(KITS_SELECT_BY_RELATED_LIBRARY, new Object[]{libraryId}, new KitMapper());
  }

  public List<Kit> listByManufacturer(String manufacturerName) throws IOException {
    return template.query(KITS_SELECT_BY_MANUFACTURER, new Object[]{manufacturerName}, new KitMapper());
  }

  public List<Kit> listKitsByType(KitType kitType) throws IOException {
    return template.query(KITS_SELECT_BY_TYPE, new Object[]{kitType.getKey()}, new KitMapper());
  }

  public long save(Kit kit) throws IOException {
    MapSqlParameterSource params = new MapSqlParameterSource();
    params.addValue("identificationBarcode", kit.getIdentificationBarcode())
            .addValue("locationBarcode", kit.getLocationBarcode())
            .addValue("lotNumber", kit.getLotNumber())
            .addValue("kitDate", kit.getKitDate())
            .addValue("kitDescriptorId", kit.getKitDescriptor().getKitDescriptorId());

    if (kit.getId() == AbstractKit.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                            .withTableName(TABLE_NAME)
                            .usingGeneratedKeyColumns("kitId");
      Number newId = insert.executeAndReturnKey(params);
      kit.setId(newId.longValue());
    }
    else {
      params.addValue("kitId", kit.getId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(KIT_UPDATE, params);
    }

    if (this.cascadeType != null && this.cascadeType.equals(CascadeType.PERSIST)) {
      if (!kit.getNotes().isEmpty()) {
        for (Note n : kit.getNotes()) {
          noteDAO.saveKitNote(kit, n);
        }
      }
    }
    return kit.getId();
  }

  public class KitMapper extends CacheAwareRowMapper<Kit> {
    public KitMapper() {
      super(Kit.class);
    }

    public KitMapper(boolean lazy) {
      super(Kit.class, lazy);
    }

    public Kit mapRow(ResultSet rs, int rowNum) throws SQLException {
      long id = rs.getLong("kitId");

      if (isCacheEnabled() && lookupCache(cacheManager) != null) {
        Element element;
        if ((element = lookupCache(cacheManager).get(DbUtils.hashCodeCacheKeyFor(id))) != null) {
          log.debug("Cache hit on map for Kit " + id);
          return (Kit)element.getObjectValue();
        }
      }
      Kit kit = null;

      try {
        KitDescriptor kd = getKitDescriptorById(rs.getInt("kitDescriptorId"));
        KitType kitType = kd.getKitType();

        if (kitType.equals(KitType.CLUSTERING)) {
          kit = new ClusterKit();
        }
        else if (kitType.equals(KitType.EMPCR)) {
          kit = new EmPcrKit();
        }
        else if (kitType.equals(KitType.LIBRARY)) {
          kit = new LibraryKit();
        }
        else if (kitType.equals(KitType.MULTIPLEXING)) {
          kit = new MultiplexingKit();
        }
        else if (kitType.equals(KitType.SEQUENCING)) {
          kit = new SequencingKit();
        }
        else {
          throw new SQLException("Unsupported KitType: "+kitType.getKey());
        }

        kit.setId(id);
        kit.setIdentificationBarcode(rs.getString("identificationBarcode"));
        kit.setLocationBarcode(rs.getString("locationBarcode"));
        kit.setLotNumber(rs.getString("lotNumber"));
        kit.setKitDate(rs.getDate("kitDate"));
        kit.setKitDescriptor(kd);
        kit.setNotes(noteDAO.listByKit(rs.getLong("kitId")));
      }
      catch (IOException e) {
        e.printStackTrace();
      }
      return kit;
    }
  }

  public KitDescriptor getKitDescriptorById(long id) throws IOException {
    List eResults = template.query(KIT_DESCRIPTOR_SELECT_BY_ID, new Object[]{id}, new KitDescriptorMapper());
    return eResults.size() > 0 ? (KitDescriptor) eResults.get(0) : null;
  }

  public KitDescriptor getKitDescriptorByPartNumber(String partNumber) throws IOException {
    List eResults = template.query(KIT_DESCRIPTOR_SELECT_BY_PART_NUMBER, new Object[]{partNumber}, new KitDescriptorMapper());
    return eResults.size() > 0 ? (KitDescriptor) eResults.get(0) : null;
  }

  public List<KitDescriptor> listAllKitDescriptors() throws IOException {
    return template.query(KIT_DESCRIPTORS_SELECT, new KitDescriptorMapper());
  }
  
  public List<KitDescriptor> listKitDescriptorsByType(KitType kitType) throws IOException {
    return template.query(KIT_DESCRIPTORS_SELECT_BY_TYPE, new Object[]{kitType.getKey()}, new KitDescriptorMapper());
  }

  public List<KitDescriptor> listKitDescriptorsByPlatform(PlatformType platformType) throws IOException {
    return template.query(KIT_DESCRIPTORS_SELECT_BY_PLATFORM, new Object[]{platformType}, new KitDescriptorMapper());
  }
  
  public long saveKitDescriptor(KitDescriptor kd) throws IOException {
    //log.info("Saving " + kd.toString() + " : " + kd.getKitType() + " : " + kd.getPlatformType());
    MapSqlParameterSource params = new MapSqlParameterSource();

    params.addValue("name", kd.getName())
            .addValue("version", kd.getVersion())
            .addValue("manufacturer", kd.getManufacturer())
            .addValue("partNumber", kd.getPartNumber())
            .addValue("stockLevel", kd.getStockLevel())
            .addValue("kitType", kd.getKitType().getKey())
            .addValue("platformType", kd.getPlatformType().getKey());

    if (kd.getKitDescriptorId() == KitDescriptor.UNSAVED_ID) {
      SimpleJdbcInsert insert = new SimpleJdbcInsert(template)
                            .withTableName("KitDescriptor")
                            .usingGeneratedKeyColumns("kitDescriptorId");
      Number newId = insert.executeAndReturnKey(params);
      kd.setKitDescriptorId(newId.longValue());
    }
    else {
      params.addValue("kitDescriptorId", kd.getKitDescriptorId());
      NamedParameterJdbcTemplate namedTemplate = new NamedParameterJdbcTemplate(template);
      namedTemplate.update(KIT_DESCRIPTOR_UPDATE, params);
    }

    return kd.getKitDescriptorId();
  }

  public class KitDescriptorMapper implements RowMapper<KitDescriptor> {
    public KitDescriptor mapRow(ResultSet rs, int rowNum) throws SQLException {
      KitDescriptor kd = new KitDescriptor();
      kd.setKitDescriptorId(rs.getLong("kitDescriptorId"));
      kd.setName(rs.getString("name"));
      kd.setVersion(rs.getDouble("version"));
      kd.setManufacturer(rs.getString("manufacturer"));
      kd.setPartNumber(rs.getString("partNumber"));
      kd.setStockLevel(rs.getInt("stockLevel"));
      
      kd.setKitType(KitType.get(rs.getString("kitType")));

      //log.info("Set kit type for descriptor " + kd.getKitDescriptorId() + " to " + kd.getKitType());

      kd.setPlatformType(PlatformType.get(rs.getString("platformType")));
      
      //log.info("Set platform type for descriptor " + kd.getKitDescriptorId() + " to " + kd.getPlatformType());
      return kd;
    }
  }
}
